﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using TrainingWorkshop.Models;

namespace TrainingWorkshop.DAL
{
    public class CourseDAL
    {
        GetConnection cn = new GetConnection();
        SqlConnection con = new SqlConnection();
        DataSet ds = new DataSet();
        /// <summary>
        /// GetAllCourses from database
        /// </summary>
        /// <returns>List CourseShow a new class in Models</returns>
        public List<CourseShow> GetAllCourses()
        {
            //connect to database
            con = cn.ConnectDatabase();//=(case when (CourseStatus='true')then 'Open' else 'Closed' end)  
            string sql = "select CourseId ,CourseName,[Teacher].TeacherName,[Level].LevelName,CourseStatus from Course, Level,Teacher where [Course].TeacherId=[Teacher].TeacherId and [Course].LevelId=[Level].LevelId";
            List<CourseShow> courseshowlist = new List<CourseShow>();
            try
            {

                //open the connection
                con.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
                //fill the sqldataadapter with the records selected from the database
                adapter.Fill(ds, "Course");
                Course course = new Course();
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    CourseShow courses = new CourseShow();
                    courses.CourseId=Convert.ToInt32(row[0].ToString().Trim());
                    courses.CourseName = row[1].ToString().Trim();
                    courses.TeacherName = row[2].ToString().Trim();
                    courses.LevelName = row[3].ToString().Trim();
                    courses.CourseStatus = Convert.ToBoolean(row[4].ToString().Trim());
                    courseshowlist.Add(courses);
                }
                return courseshowlist;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
        }
        /// <summary>
        /// GetSelectedCourses based on the params
        /// </summary>
        /// <param name="name"></param>
        /// <param name="status"></param>
        /// <returns>List CourseShow in Models</returns>
        public List<CourseShow> GetSelectedCourses(string name, bool status)
        {
            con = cn.ConnectDatabase();
            string sql = "select CourseId ,CourseName,[Teacher].TeacherName,[Level].LevelName,CourseStatus from Course, Level,Teacher where [Course].TeacherId=[Teacher].TeacherId and [Course].LevelId=[Level].LevelId and CourseName Like '%'+@name+'%' and CourseStatus=@status";
            List<CourseShow> courseshowlist = new List<CourseShow>();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlParameter Sp = new SqlParameter("name", name);
                SqlParameter Sp1 = new SqlParameter("status", status);
                cmd.Parameters.Add(Sp);
                cmd.Parameters.Add(Sp1);
                con.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "Course");
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    CourseShow courses = new CourseShow();
                    courses.CourseId = Convert.ToInt32(row[0].ToString().Trim());
                    courses.CourseName = row[1].ToString().Trim();
                    courses.TeacherName = row[2].ToString().Trim();
                    courses.LevelName = row[3].ToString().Trim();
                    courses.CourseStatus = Convert.ToBoolean(row[4].ToString().Trim());
                    courseshowlist.Add(courses);
                }
                return courseshowlist;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// ShowStatus
        /// When delete should return the course's status
        /// </summary>
        /// <param name="id"></param>
        /// <returns>int</returns>
        public int ShowStatus(string id)
        {
            con = cn.ConnectDatabase();
            string sql = "select CourseStatus from Course where CourseId='" + id + "'";
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                bool status;
                while (reader.Read())
                {
                    status = (Boolean)reader["CourseStatus"];
                    if (status)
                    {
                        return 1;
                    }
                    else
                    {
                        return 0;
                    }
                }
                reader.Close();
                return -1;
                
            }
            finally
            {
                
                con.Close();
            }
        }
        /// <summary>
        /// Delete courses based on CourseId
        /// </summary>
        /// <param name="id"></param>
        public void DeleteCourse(string id)
        {
            con = cn.ConnectDatabase();
            string sql = "delete from Course where CourseId='" + id + "'";
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// UpdateCourse
        /// </summary>
        /// <param name="course"></param>
        public void UpdateCourse(Course course)
        {
            con = cn.ConnectDatabase();
            string sql = "Update Course set CourseName=@CourseName , TeacherId=@TeacherId ,LevelId=@LevelId , CourseStatus=@CourseStatus where CourseId=@CourseId  ";
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                SqlParameter sp1 = new SqlParameter("CourseId", course.CourseId);
                SqlParameter sp2 = new SqlParameter("CourseName", course.CourseName);
                SqlParameter sp3 = new SqlParameter("TeacherId", course.TeacherId);
                SqlParameter sp4 = new SqlParameter("LevelId", course.LevelId);
                SqlParameter sp5 = new SqlParameter("CourseStatus", course.CourseStatus);
                cmd.Parameters.Add(sp1);
                cmd.Parameters.Add(sp2);
                cmd.Parameters.Add(sp3);
                cmd.Parameters.Add(sp4);
                cmd.Parameters.Add(sp5);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "Course");
                cmd.ExecuteNonQuery();

            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// Change courses' status from open to close
        /// </summary>
        /// <param name="course"></param>
        public void ChangeToClose(Course course)
        {
            con = cn.ConnectDatabase();
            string sql = "Update Course set CourseStatus='false' where CourseId=@CourseId";
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                SqlParameter sp1 = new SqlParameter("CourseId", course.CourseId);
                cmd.Parameters.Add(sp1);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "Course");
                cmd.ExecuteNonQuery();

            }
            finally
            {
                con.Close();
            }
        }


    }

}
